package com.boesch.electroOffice.dataaccess.daoImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.MeasurementDao;
import com.boesch.electroOffice.dataaccess.mapper.MeasurementEntryMapper;
import com.boesch.electroOffice.dataaccess.mapper.MeasurementMapper;
import com.boesch.electroOffice.model.electroOffice.Measurement;
import com.boesch.electroOffice.model.electroOffice.MeasurementEntry;
import com.boesch.electroOffice.utils.DateUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the database for class Measurement and MeasurementEntry.
 * If a Exception is thrown, 
 * it will be unpacked to a illegalDataAccessException 
 * including a return code which can be shown to the user.
 * 
 * @author Ralph Koster
 * 
 */
@Repository
public class MeasurementDaoImpl implements MeasurementDao, Serializable {

	private static final long serialVersionUID = -8966475257722354964L;
	private static final Log log = LogFactoryUtil.getLog(MeasurementDaoImpl.class);

	@Autowired
	private JdbcTemplate eoJdbcTemplate;
	@Autowired
	private TransactionTemplate eoTransactionTemplate;

	@Value("${electroOffice.queryReadMeasurementsByAppointmentId}")
	private String queryReadMeasurementsByAppointmentId;
	@Value("${electroOffice.queryReadMeasurementById}")
	private String queryReadMeasurementById;
	@Value("${electroOffice.queryReadMeasurementEntriesByMeasurementId}")
	private String queryReadMeasurementEntriesByMeasurementId;
	@Value("${electroOffice.queryCreateMeasurement}")
	private String queryCreateMeasurement;
	@Value("${electroOffice.queryCreateMeasurementEntry}")
	private String queryCreateMeasurementEntry;
	@Value("${electroOffice.queryUpdateMeasurement}")
	private String queryUpdateMeasurement;

	/**
	 * {@inheritDoc}
	 */
	public List<Measurement> readMeasurementsByAppointmentId(int appointmentId) throws IllegalDataAccessException {
		List<Measurement> measurements = new ArrayList<Measurement>();
		try {
			measurements = eoJdbcTemplate.query(queryReadMeasurementsByAppointmentId, new Object[] { appointmentId }, new MeasurementMapper());
			return measurements;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Measurement readMeasurementById(Integer measurementId) throws IllegalDataAccessException {
		Measurement measurement = null;
		List<Measurement> measurements = eoJdbcTemplate.query(queryReadMeasurementById, new Object[] { measurementId }, new MeasurementMapper());
		if (null != measurements && measurements.size() == 1) {
			measurement = measurements.get(0);
			List<MeasurementEntry> entries = readMeasurementEntrysByMeasurementId(measurementId);
			measurement.setMeasurementEntries(entries);
			return measurement;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<MeasurementEntry> readMeasurementEntrysByMeasurementId(int measurementId) throws IllegalDataAccessException {
		List<MeasurementEntry> measurementEntries = new ArrayList<MeasurementEntry>();
		try {
			measurementEntries = eoJdbcTemplate.query(queryReadMeasurementEntriesByMeasurementId, new Object[] { measurementId }, new MeasurementEntryMapper());
			return measurementEntries;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String saveMeasurementOnDatabase(final Measurement measurement, final User currentUser) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				public void doInTransactionWithoutResult(TransactionStatus status) {
					eoJdbcTemplate.update(new PreparedStatementCreator() {
						public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
							PreparedStatement ps = connection.prepareStatement(queryCreateMeasurement, Statement.RETURN_GENERATED_KEYS);
							ps.setString(1, measurement.getObject());
							ps.setString(2, measurement.getEnergyMeterNumber());
							ps.setString(3, measurement.getLocation());
							ps.setDate(4, DateUtils.convertLocalDateToDate(measurement.getMeasurementDate()));
							ps.setLong(5, measurement.getLrUserId());
							ps.setString(6, currentUser.getFullName());
							ps.setTimestamp(7, DateUtils.getCurrentTimestamp());
							ps.setInt(8, 1);
							return ps;
						}
					}, keyHolder);

					List<MeasurementEntry> measurementEntrys = measurement.getMeasurementEntries();
					for (final MeasurementEntry measurementEntry : measurementEntrys) {
						eoJdbcTemplate.update(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
								PreparedStatement ps = connection.prepareStatement(queryCreateMeasurementEntry, Statement.RETURN_GENERATED_KEYS);
								ps.setString(1, measurementEntry.getCurrentCircuitNumber());
								ps.setString(2, measurementEntry.getDescription());
								ps.setString(3, measurementEntry.getConductorCrossSection());
								ps.setString(4, measurementEntry.getCharacteristic());
								ps.setString(5, measurementEntry.getRatedCurrent());
								ps.setString(6, measurementEntry.getPeConductibility());
								ps.setString(7, measurementEntry.getShortCircuitCurrent());
								ps.setString(8, measurementEntry.getInsulatingResistance());
								ps.setBoolean(9, measurementEntry.isInsulatingResistanceIsBigger999());
								ps.setString(10, measurementEntry.getFiRatedCurrent());
								ps.setString(11, measurementEntry.getFiReleaseCurrent());
								ps.setString(12, measurementEntry.getFiTimeInMillis());
								ps.setBoolean(13, measurementEntry.isRotatingFieldClockwise());
								ps.setString(14, currentUser.getFullName());
								ps.setTimestamp(15, DateUtils.getCurrentTimestamp());
								ps.setInt(16, 1);
								ps.setInt(17, keyHolder.getKey().intValue());
								return ps;
							}

						});
					}
				}
			});
			log.debug("Inserted measurement with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String updateMeasurementOnDatabase(final Measurement measurement, final User currentUser) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				public void doInTransactionWithoutResult(TransactionStatus status) {
					eoJdbcTemplate.update(new PreparedStatementCreator() {
						public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
							PreparedStatement ps = connection.prepareStatement(queryUpdateMeasurement, Statement.RETURN_GENERATED_KEYS);
							ps.setString(1, measurement.getObject());
							ps.setString(2, measurement.getEnergyMeterNumber());
							ps.setString(3, measurement.getLocation());
							ps.setDate(4, DateUtils.convertLocalDateToDate(measurement.getMeasurementDate()));
							ps.setLong(5, measurement.getLrUserId());
							ps.setString(5, currentUser.getFullName());
							ps.setInt(7, measurement.getVersion() + 1);
							return ps;
						}
					}, keyHolder);

					List<MeasurementEntry> measurementEntrys = measurement.getMeasurementEntries();
					for (final MeasurementEntry measurementEntry : measurementEntrys) {
						eoJdbcTemplate.update(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
								PreparedStatement ps = connection.prepareStatement(queryCreateMeasurementEntry, Statement.RETURN_GENERATED_KEYS);
								ps.setString(1, measurementEntry.getCurrentCircuitNumber());
								ps.setString(2, measurementEntry.getDescription());
								ps.setString(3, measurementEntry.getConductorCrossSection());
								ps.setString(4, measurementEntry.getCharacteristic());
								ps.setString(5, measurementEntry.getRatedCurrent());
								ps.setString(6, measurementEntry.getPeConductibility());
								ps.setString(7, measurementEntry.getShortCircuitCurrent());
								ps.setString(8, measurementEntry.getInsulatingResistance());
								ps.setBoolean(9, measurementEntry.isInsulatingResistanceIsBigger999());
								ps.setString(10, measurementEntry.getFiRatedCurrent());
								ps.setString(11, measurementEntry.getFiReleaseCurrent());
								ps.setString(12, measurementEntry.getFiTimeInMillis());
								ps.setBoolean(13, measurementEntry.isRotatingFieldClockwise());
								ps.setString(14, currentUser.getFullName());
								ps.setInt(15, measurementEntry.getVersion() + 1);
								return ps;
							}

						});
					}
				}
			});
			log.debug("Inserted measurement with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}
}
